# -*- coding: utf-8 -*-
import sqlite3
conn=sqlite3.connect(r'cd.db3')
conn.text_factory = str
try:
	conn.execute('''CREATE TABLE rpt_companysource(
		companyid TEXT REFERENCES [company]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		companyname TEXT,
		sourceid INTEGER REFERENCES [source]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		sourcename TEXT,
		boxs INTEGER NOT NULL DEFAULT 0,
		cds INTEGER,
		PRIMARY KEY ([companyid],[sourceid])
	)''')
	conn.commit()
except:
	pass

conn.execute('replace into rpt_companysource select cd_companyid,company,cd_sourceid,source,sum(cd_box),count(cd_sourceid) from cd,company,source where cd.cd_companyid=company.id and cd.cd_sourceid=source.id group by cd_companyid,cd_sourceid')
conn.commit()
conn.close()
